﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_FIT_FIGURE_MGT_Select_Paging')
	BEGIN
		DROP Procedure usp_UPDMS_FIT_FIGURE_MGT_Select_Paging
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_FIT_FIGURE_MGT_Select_Paging
**	Desc : 신체지수관리를 조회한다.
**	Test Exec Query : Exec usp_UPDMS_FIT_FIGURE_MGT_Select_Paging 0, 20, '2013-06-01', '2014-05-20', '', '', 'ko'
**	Called by : Fit_Dac_UPDMS_FIT_FIGURE_MGT.cs
**	Program ID : Fit1012m
**	Auth : 송시명
**	Date : 2014-05-20
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**			
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_FIT_FIGURE_MGT_Select_Paging]
@li_st_page int,
@li_ed_page int,
@ls_measure_dt_from nvarchar(10),
@ls_measure_dt_to nvarchar(10),
@ls_memo nvarchar(max),
@ls_file_exist varchar(1),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--첨부파일존재여부
DECLARE @file_exist int
IF @ls_file_exist = ''
BEGIN
    SET @file_exist = 0
END
ELSE
BEGIN
    SET @file_exist = 1
END

DECLARE @TMP_RSLT TABLE(
  Rnum bigint,
  Seq int,
  Measure_Dt nvarchar(10),
  Day_Of_Week nvarchar(9),
  Body_Weight decimal(10, 1),
  BMI decimal(10, 2),
  Soft_Lean_Mass decimal(10, 2),
  Body_Fat_Mass decimal(10, 2),
  Body_Fat_Ratio decimal(10, 2),
  Waist_Hip_Fat_Ratio decimal(10, 2),
  Memo nvarchar(max),
  Attach_File_Info nvarchar(200)
)

INSERT @TMP_RSLT
SELECT a.Rnum,
       a.Seq,
       a.Measure_Dt,
       a.Day_Of_Week,
       a.Body_Weight,
       a.BMI,
       a.Soft_Lean_Mass,
       a.Body_Fat_Mass,
       a.Body_Fat_Ratio,
       a.Waist_Hip_Fat_Ratio,
       a.Memo,
       a.Attach_File_Info
  FROM (
       SELECT ROW_NUMBER() OVER(ORDER BY uffm.Measure_Dt DESC) AS Rnum,
              uffm.Seq,
              uffm.Measure_Dt,
              CASE @ls_lang_set WHEN 'ko' THEN umd.Day_K_Nm ELSE umd.Day_E_Nm END Day_Of_Week,
              uffm.Body_Weight,
              uffm.BMI,
              uffm.Soft_Lean_Mass,
              uffm.Body_Fat_Mass,
              uffm.Body_Fat_Ratio,
              uffm.Waist_Hip_Fat_Ratio,
              uffm.Memo,
              CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File_Image(uffm.Seq, 'Fit1012m') <= 0 THEN ''
                   ELSE '<div title="첨부파일" class="sbtn sbtn_fdisk" onclick="popupMultiImage(''' + CONVERT(VARCHAR, uffm.Seq) + ''',''Fit1012m'');"></div>'
              END Attach_File_Info
         FROM UPDMS_FIT_FIGURE_MGT AS uffm WITH(NOLOCK)
         JOIN UPDMS_MST_DATE AS umd WITH(NOLOCK) ON uffm.Measure_Dt = umd.Dt
        WHERE uffm.Measure_Dt >= @ls_measure_dt_from
          AND uffm.Measure_Dt <= @ls_measure_dt_to
          AND uffm.Memo LIKE '%' + @ls_memo + '%'
          AND dbo.ufn_UPDMS_Get_Exist_Attach_File(uffm.Seq, 'Fit1012m') >= @file_exist
       ) a
 WHERE a.Rnum > @li_st_page AND a.Rnum <= @li_ed_page


--마지막 측정일의 증감치
DECLARE @last_body_weight decimal(10, 1)

SELECT @last_body_weight = Body_Weight
  FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
 WHERE Measure_Dt = (SELECT MAX(Measure_Dt)
                       FROM UPDMS_FIT_FIGURE_MGT WITH(NOLOCK)
                      WHERE Measure_Dt < (SELECT MIN(Measure_Dt) FROM @TMP_RSLT)
                    )


--이전 측정일대비 증감치 포함  
SELECT a.Seq,
       a.Measure_Dt,
       a.Day_Of_Week,
       a.Body_Weight,
       ISNULL(ISNULL(a.Body_Weight - b.Body_Weight, a.Body_Weight - @last_body_weight), 0.0) AS Diff_Body_Weight,
       a.BMI,
       a.Soft_Lean_Mass,
       a.Body_Fat_Mass,
       a.Body_Fat_Ratio,
       a.Waist_Hip_Fat_Ratio,
       a.Memo,
       a.Attach_File_Info
  FROM @TMP_RSLT a
  LEFT OUTER JOIN @TMP_RSLT b ON a.Rnum = (b.Rnum - 1)
 ORDER BY a.Measure_Dt DESC

GO